/*
 * @(#)SqlLinkDb.java	0.1 01/01/04
 *
 * Copyright 2004 Fabio Gasparetti. All rights reserved.
 */
package org.tabularium.net.linkdb;

import java.util.*;
import java.util.Date;
import java.sql.*;

/**
 * Persistence implementation of <code>LinkDb</code> interface based on a SQL
 * data-base that can be managed through a jdbc driver. Note: input of duplicate
 * entries are allowed but getters return only distinct links.
 * 
 * @author Fabio Gasparetti
 * @version 0.1, 01/01/06
 */

public class SqlLinkDb implements LinkDb {

	/**
	 * Sql implementation is based on indexes and MySql limits the length of
	 * keys to MAX_URL_LENGTH characters. For this reason, url is truncated to
	 * MAX_URL_LENGTH, that is, if two urls differs in two characters after the
	 * MAX_URL_LENGTH position, they will be considered the same url.
	 */
	public static final int MAX_URL_LENGTH = 1000;

	protected Connection connection;

	protected String dbname = null;

	protected static final String readLock = "LOCK TABLES outLinks READ, urls READ, urls AS urls1 READ, urls AS urls2 READ;";

	protected static final String writeLock = "LOCK TABLES outLinks WRITE, urls WRITE;";

	protected static final String urlReadLock = "LOCK TABLES urls READ;";

	protected static final String unlock = "UNLOCK TABLES;";

	protected static final String urlsTable = "CREATE TABLE urls "
			+ "(id INT NOT NULL AUTO_INCREMENT, url BLOB NOT NULL ,  "
			+ "PRIMARY KEY id (id), " + "UNIQUE KEY url_idx (url("+MAX_URL_LENGTH+"))) "
			+ "MAX_ROWS=100000000 AVG_ROW_LENGTH=100;";

	protected static final String outLinksTable = "CREATE TABLE outLinks "
			+ "(id INT NOT NULL AUTO_INCREMENT, idurl INT NOT NULL,  ts DATETIME, "
			+ "idlink INT NOT NULL, "+ " KEY ts_idx(ts), " + "PRIMARY KEY id (id), "
			+ "KEY idurl_idx (idurl), " + "KEY idlink_idx (idlink));";

	/* URL SQLs */
	protected static final String getUrlId = "SELECT id FROM urls WHERE url=?;";

	protected static final String addUrl = "INSERT INTO urls (url) VALUES (?);";

	/* LINK SQLs */
	protected static final String resetIdUrls = "DELETE FROM urls;";

	protected static final String getUrlIds = "SELECT id FROM urls;";

	protected static final String numUrls = "SELECT COUNT(*) FROM urls;";

	protected static final String getUrl = "SELECT url FROM urls WHERE id=?;";

	protected static final String getLinkId = "SELECT id FROM outLinks WHERE idurl=? AND idlink=?;";

	protected static final String getUrlsByLinkId = "SELECT idurl, idlink FROM outLinks WHERE id=?;";

	protected static final String resetOutLinks = "DELETE FROM outLinks;";

	protected static final String addLink = "INSERT INTO outLinks "
			+ "(idurl, idlink) VALUES (?, ?);";

	protected static final String deleteLink = "DELETE FROM outLinks "
			+ "WHERE idurl=? AND idlink=?;";

	protected static final String getOutLinks = "SELECT DISTINCT urls1.url "
			+ "FROM outLinks, urls AS urls1, urls AS urls2 "
			+ "WHERE outLinks.idlink=urls1.id AND outLinks.idurl=urls2.id "
			+ "AND urls2.url=?;";

	protected static final String getOutLinksOrderedByTimestamp = "SELECT DISTINCT urls1.url "
			+ "FROM outLinks, urls AS urls1, urls AS urls2 "
			+ "WHERE outLinks.idlink=urls1.id AND outLinks.idurl=urls2.id "
			+ "AND urls2.url=? ORDER BY outLinks.ts;";

	protected static final String getOutLinksById = "SELECT DISTINCT urls1.id "
			+ "FROM outLinks, urls AS urls1, urls AS urls2 "
			+ "WHERE outLinks.idlink=urls1.id AND outLinks.idurl=urls2.id "
			+ "AND urls2.id=?;";

	protected static final String getInLinks = "SELECT DISTINCT urls2.url "
			+ "FROM outLinks, urls AS urls1, urls AS urls2 "
			+ "WHERE outLinks.idlink=urls1.id AND outLinks.idurl=urls2.id "
			+ "AND urls1.url=?;";

	protected static final String getInLinksOrderedByTimestamp = "SELECT DISTINCT urls2.url "
			+ "FROM outLinks, urls AS urls1, urls AS urls2 "
			+ "WHERE outLinks.idlink=urls1.id AND outLinks.idurl=urls2.id "
			+ "AND urls1.url=? ORDER BY outLinks.ts;";

	protected static final String getInLinksById = "SELECT DISTINCT urls2.id "
			+ "FROM outLinks, urls AS urls1, urls AS urls2 "
			+ "WHERE outLinks.idlink=urls1.id AND outLinks.idurl=urls2.id "
			+ "AND urls1.id=?;";

	protected static final String numLinks = "SELECT COUNT(*) FROM outLinks;";

	/**
	 * Initializes the link database.
	 * <p>
	 * Calls this method after having created a new <code>SqlLinkDb</code>
	 * object.
	 * <p>
	 * An example using MySql db:
	 * <code>init("com.mysql.jdbc.Driver", "jdbc:mysql://127.0.0.1/db_name?user=root", "jdbc:mysql://127.0.0.1/mysql?user=root", "db_name", true);</code>
	 * <p>
	 * where <code>db_name</code> is the name of the link db.
	 * 
	 * @param driver
	 *            the name of the class of the db driver
	 * @param url
	 *            the url used to access the db
	 * @param creationUrl
	 *            the url used by the driver to create a new db
	 * @param dbname
	 *            the name of the db
	 * @param reset
	 *            if true the db will be erased
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public void init(String driver, String url, String creationUrl,
			String dbname, boolean reset) throws ClassNotFoundException,
			SQLException {
		PreparedStatement stat;
		this.dbname = dbname;
		if ((driver == null) || (url == null)) {
			throw new IllegalArgumentException();
		}

		try {
			Class.forName(driver).newInstance();
		} catch (Exception ex) {
			// IllegalAccessException and InstantiationException exceptions
			throw new ClassNotFoundException("Unable to instantiate " + driver
					+ " class");
		}

		try {
			connection = DriverManager.getConnection(url);
		} catch (SQLException ex) {
			if (ex.getErrorCode() == 1049) { // unknown database
				reset = true;
			} else {
				throw ex;
			}
		}

		if (reset) {
			createDb(creationUrl);
		}
		stat = connection.prepareStatement("USE " + dbname + ";");
		stat.execute();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#clear()
	 */
	public void clear() throws LinkDbException {
		try {
			PreparedStatement stat;
			stat = connection.prepareStatement(writeLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(resetOutLinks);
			stat.executeUpdate();
			stat.close();
			stat = connection.prepareStatement(resetIdUrls);
			stat.executeUpdate();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#size()
	 */
	public int size() throws LinkDbException {
		int n = -1;
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(numLinks);
			rs = stat.executeQuery();
			if (rs.next()) {
				n = rs.getInt(1);
			}
			rs.close();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return n;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#inLinks()
	 */
	public List inLinks(String url) throws LinkDbException {
		ArrayList links = new ArrayList();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getInLinks);
			stat.setString(1, url);
			rs = stat.executeQuery();
			while (rs.next()) {
				links.add(rs.getString(1));
			}
			rs.close();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return links;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#inLinks()
	 */
	public List inLinksOrderedByTimestamp(String url) throws LinkDbException {
		ArrayList links = new ArrayList();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getInLinksOrderedByTimestamp);
			stat.setString(1, url);
			rs = stat.executeQuery();
			while (rs.next()) {
				links.add(rs.getString(1));
			}
			rs.close();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return links;
	}

	/*
	 * Returns an Integer list.
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#inLinks()
	 */
	public List inLinks(int idUrl) throws LinkDbException {
		ArrayList links = new ArrayList();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getInLinksById);
			stat.setInt(1, idUrl);
			rs = stat.executeQuery();
			while (rs.next()) {
				links.add(new Integer(rs.getInt(1)));
			}
			rs.close();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return links;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#outLinks()
	 */
	public List outLinks(String url) throws LinkDbException {
		ArrayList links = new ArrayList();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getOutLinks);
			stat.setString(1, url);
			rs = stat.executeQuery();
			while (rs.next()) {
				links.add(rs.getString(1));
			}
			rs.close();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return links;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#outLinks()
	 */
	public List outLinksOrderedByTimestamp(String url) throws LinkDbException {
		ArrayList links = new ArrayList();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getOutLinksOrderedByTimestamp);
			stat.setString(1, url);
			rs = stat.executeQuery();
			while (rs.next()) {
				links.add(rs.getString(1));
			}
			rs.close();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return links;
	}

	/*
	 * Returns an Integer list.
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#outLinks()
	 */
	public List outLinks(int idUrl) throws LinkDbException {
		ArrayList links = new ArrayList();
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getOutLinksById);
			stat.setInt(1, idUrl);
			rs = stat.executeQuery();
			while (rs.next()) {
				links.add(new Integer(rs.getInt(1)));
			}
			rs.close();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return links;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#browse()
	 */
	public UrlIterator browse() throws LinkDbException {
		int linkIds[] = new int[0];
		try {
			ResultSet rs;
			int dim = 0;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			// instead of going through a dynamic list,
			// get the size of the result set and create a static array
			stat = connection.prepareStatement(numUrls);
			rs = stat.executeQuery();
			if (rs.next()) {
				dim = rs.getInt(1);
			}
			rs.close();
			stat.close();
			linkIds = new int[dim];
			stat = connection.prepareStatement(getUrlIds);
			rs = stat.executeQuery();
			for (int i = 0; rs.next(); i++) {
				linkIds[i] = rs.getInt(1);
			}
			rs.close();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return new SqlUrlIterator(this, linkIds);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#browse()
	 */
	public int numUrls() throws LinkDbException {
		int dim = 0;
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			// instead of going through a dynamic list,
			// get the size of the result set and create a static array
			stat = connection.prepareStatement(numUrls);
			rs = stat.executeQuery();
			if (rs.next()) {
				dim = rs.getInt(1);
			}
			rs.close();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return dim;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#addLink()
	 */
	public void addLink(String url, String link) throws LinkDbException {
		try {
			PreparedStatement stat;
			stat = connection.prepareStatement(writeLock);
			stat.execute();
			stat.close();
			addLinkNoLock(url, link);
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#addLink()
	 */
	public void addLink(String url, String link, Date ts) throws LinkDbException {
		try {
			PreparedStatement stat;
			stat = connection.prepareStatement(writeLock);
			stat.execute();
			stat.close();
			addLinkNoLock(url, link, ts);
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#addLinks()
	 */
	public void addLinks(String url, Collection links) throws LinkDbException {
		try {
			PreparedStatement stat;
			stat = connection.prepareStatement(writeLock);
			stat.execute();
			stat.close();
			Iterator iter = links.iterator();
			while (iter.hasNext()) {
				String link = (String) (iter.next());
				addLinkNoLock(url, link);
			}
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#addLinks()
	 */
	public void addLinks(String url, Collection links, Date ts) throws LinkDbException {
		try {
			PreparedStatement stat;
			stat = connection.prepareStatement(writeLock);
			stat.execute();
			stat.close();
			Iterator iter = links.iterator();
			while (iter.hasNext()) {
				String link = (String) (iter.next());
				addLinkNoLock(url, link, ts);
			}
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.tabularium.net.linkdb.LinkDb#removeLink()
	 */
	public void removeLink(String url, String link) throws LinkDbException {
		int idurl, idlink;
		try {
			PreparedStatement stat;
			stat = connection.prepareStatement(writeLock);
			stat.execute();
			stat.close();
			idurl = getUrlId(url.toString());
			idlink = getUrlId(link.toString());
			stat = connection.prepareStatement(deleteLink);
			stat.setInt(1, idurl);
			stat.setInt(2, idlink);
			stat.executeUpdate();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
	}

	protected void addLinkNoLock(String url, String link)
			throws java.sql.SQLException {
		String newUrl = url, newLink = link;
		if (url.length() > SqlLinkDb.MAX_URL_LENGTH)
			newUrl = url.substring(0, SqlLinkDb.MAX_URL_LENGTH);
		if (link.length() > SqlLinkDb.MAX_URL_LENGTH)
			newLink = link.substring(0, SqlLinkDb.MAX_URL_LENGTH);
		
		int idurl = getUrlId(newUrl);
		if (idurl == -1) {
			idurl = addUrl(newUrl);
		}
		int idlink = getUrlId(newLink);
		if (idlink == -1) {
			idlink = addUrl(newLink);
		}
		if (getLinkId(idurl, idlink) == -1) {
			PreparedStatement stat;
			stat = connection.prepareStatement(addLink);
			stat.setInt(1, idurl);
			stat.setInt(2, idlink);
			stat.execute();
			stat.close();
		}
	}

	protected void addLinkNoLock(String url, String link, Date ts)
			throws java.sql.SQLException {
		String newUrl = url, newLink = link;
		if (url.length() > SqlLinkDb.MAX_URL_LENGTH)
			newUrl = url.substring(0, SqlLinkDb.MAX_URL_LENGTH);
		if (link.length() > SqlLinkDb.MAX_URL_LENGTH)
			newLink = link.substring(0, SqlLinkDb.MAX_URL_LENGTH);
		
		int idurl = getUrlId(newUrl);
		if (idurl == -1) {
			idurl = addUrl(newUrl);
		}
		int idlink = getUrlId(newLink);
		if (idlink == -1) {
			idlink = addUrl(newLink);
		}
		if (getLinkId(idurl, idlink) == -1) {
			PreparedStatement stat;
			stat = connection.prepareStatement(addLink);
			stat.setInt(1, idurl);
			stat.setInt(2, idlink);
			stat.setTimestamp(3, new java.sql.Timestamp(ts.getTime()));
			stat.execute();
			stat.close();
		}
	}
	
	public String[] urlsByLinkId(int linkId) throws LinkDbException {
		String[] ret = new String[2];
		ret[0] = null;
		ret[1] = null;
		PreparedStatement stat;
		ResultSet rs;
		try {
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();

			stat = connection.prepareStatement(getUrlsByLinkId);
			stat.setInt(1, linkId);
			rs = stat.executeQuery();
			int id1, id2;
			if (rs.next()) {
				id1 = rs.getInt(1);
				id2 = rs.getInt(2);
				ret[0] = this.getUrlById(id1);
				ret[1] = this.getUrlById(id2);
			}
			rs.close();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return ret;
	}

	/**
	 * Returns the Id associated with a link, -1 if the link doesn't exist.
	 */
	public int linkId(String url, String link) throws LinkDbException {
		int id = -1;
		PreparedStatement stat;
		try {
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			int idurl = this.getUrlId(url);
			int idlink = this.getUrlId(link);
			id = this.getLinkId(idurl, idlink);
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return id;
	}

	/**
	 * Returns the Id associated with a url, -1 if the link doesn't exist.
	 */
	public int urlId(String url) throws LinkDbException {
		int id = -1;
		PreparedStatement stat;
		try {
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			id = this.getUrlId(url);
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return id;
	}

	/*
	 * Used by the SqlUrlIterator.
	 */
	public String getUrlById(int id) throws LinkDbException {
		String u = null;
		try {
			ResultSet rs;
			PreparedStatement stat;
			stat = connection.prepareStatement(readLock);
			stat.execute();
			stat.close();
			stat = connection.prepareStatement(getUrl);
			stat.setInt(1, id);
			rs = stat.executeQuery();
			while (rs.next()) {
				u = rs.getString(1);
			}
			rs.close();
			stat.close();
			stat = connection.prepareStatement(unlock);
			stat.execute();
			stat.close();
		} catch (Exception ex) {
			throw new LinkDbException("Sql exception: " + ex.toString());
		}
		return u;
	}

	/*
	 * Returns the Id associated with an url.
	 */
	protected int getUrlId(String url) throws java.sql.SQLException {
		int id = -1;
		String newUrl = url;
		if (url.length() > SqlLinkDb.MAX_URL_LENGTH)
			newUrl = url.substring(0, SqlLinkDb.MAX_URL_LENGTH);

		ResultSet rs;
		PreparedStatement stat;
		stat = connection.prepareStatement(getUrlId);
		stat.setString(1, newUrl);

		rs = stat.executeQuery();
		if (rs.next()) {
			id = rs.getInt(1);
		}
		rs.close();
		stat.close();
		return id;
	}

	/**
	 * Returns the Id associated with a link, -1 if the link doesn't exist.
	 */
	protected int getLinkId(int idurl, int idlink) throws java.sql.SQLException {
		int id = -1;
		ResultSet rs;
		PreparedStatement stat;
		stat = connection.prepareStatement(getLinkId);
		stat.setInt(1, idurl);
		stat.setInt(2, idlink);
		rs = stat.executeQuery();
		if (rs.next()) {
			id = rs.getInt(1);
		}
		rs.close();
		stat.close();
		return id;
	}

	/**
	 * Adds in the data base a new url instance and returns the related Id.
	 * 
	 * @param url
	 * @return
	 */
	protected int addUrl(String url) throws java.sql.SQLException {
		int id = -1;
		ResultSet rs;
		String newUrl = url;
		if (url.length() > SqlLinkDb.MAX_URL_LENGTH)
			newUrl = url.substring(0, SqlLinkDb.MAX_URL_LENGTH);
		
		PreparedStatement stat;
		stat = connection.prepareStatement(addUrl);
		stat.setString(1, newUrl);
		stat.execute();
		stat.close();
		// Statement.RETURN_GENERATED_KEYS option could safe time
		// but i don't know if it works with any db server
		stat = connection.prepareStatement(getUrlId);
		stat.setString(1, newUrl);
		rs = stat.executeQuery();
		if (rs.next()) {
			id = rs.getInt(1);
		}
		rs.close();
		stat.close();
		return id;
	}

	/**
	 * If you want override this method, just call it and than call
	 * prepareStatement to create further tables.
	 * 
	 * @param url
	 * @throws SQLException
	 */
	protected void createDb(String url) throws SQLException {
		connection = DriverManager.getConnection(url);

		PreparedStatement stat;
		stat = connection.prepareStatement("DROP DATABASE IF EXISTS " + dbname
				+ ";");
		stat.execute();
		stat.close();
		stat = connection.prepareStatement("CREATE DATABASE " + dbname + ";");
		stat.execute();
		stat.close();
		stat = connection.prepareStatement("USE " + dbname + ";");
		stat.execute();
		stat.close();
		stat = connection.prepareStatement(outLinksTable);
		stat.execute();
		stat.close();
		stat = connection.prepareStatement(urlsTable);
		stat.execute();
		stat.close();
	}

	public static void main(String[] args) {
		SqlLinkDb q = null;
		try {

			q = new SqlLinkDb();
			q.init("com.mysql.jdbc.Driver",
					"jdbc:mysql://127.0.0.1/crawler_data?user=root",
					"jdbc:mysql://127.0.0.1/mysql?user=root", "crawler_data",
					true);
			for (int i = 0; i < 1000000; i++) {
				System.out.println("i: " + i);
				System.out.print("size2: " + q.size());
				int id = q
						.getUrlId("http://www.2usenet.com/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
				String s = "";
				for (int j = 0; j < 100; j++) {
					s = s + "x";
					q.addLink("a", Long.toString((long)Math.random()*1000), new Date((long)Math.random()*1000));
					System.out.println("len: " + s.length());
				}
				// q.addLink("http://www.2usenet.com/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx2",
				// "http://www.2usenet.com/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1");
				// q.addLink("http://www.2usenet.com/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx2",
				// "http://www.2usenet.com/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1");
				q.addLink(
						"http://www.2usenet.com/?epl=04070060VGsLXARSAQFQAkQHVwgHWg9aB1oG",
						"2");
				q.addLink("2", "3");
				q.addLink("1", "3");
				List l1 = q.outLinks("a");
				Iterator i1 = l1.iterator();
				while (i1.hasNext()) {
					String u = (String) (i1.next());
					System.out.print("unordered"+ u + " ");
				}
				l1 = q.outLinksOrderedByTimestamp("a");
				i1 = l1.iterator();
				while (i1.hasNext()) {
					String u = (String) (i1.next());
					System.out.print("ordered" + u + " ");
				}
				List l2 = q.outLinks("1");
				i1 = l2.iterator();
				while (i1.hasNext()) {
					String u = (String) (i1.next());
					System.out.print(u + " ");
				}
				id = q.linkId("1", "2");
				System.out.println("id 1->2: " + id);
				System.out.println("url1:" + q.urlsByLinkId(id)[0] + " url2:"
						+ q.urlsByLinkId(id)[1]);
				// System.out.println("id 2->3: "+q.linkId("2", "3"));
				q.removeLink("1", "2");
				q.removeLink("2", "3");
				q.removeLink("1", "3");
				q.removeLink("1", "5");
				System.out.println(" size2: " + q.size());
			}
		} catch (Exception ex) {
			System.out.println(ex.toString());
			ex.printStackTrace();
		}
	}

}